﻿# coding:utf-8
import io
import json
import sys

import openpyxl
import pandas as pd
from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment, colors
from openpyxl.utils import get_column_letter
import openpyxl

sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

import requests


# total_row_index 总计在第几行
def set_style(file_path, ext, sheet_name, first, feeCurrency_list):
    cn_name = NamedStyle(name='cn_name',
                         font=Font(name=u'宋体', size=24, bold=False),
                         alignment=Alignment(horizontal='center', vertical='center'))
    en_name = NamedStyle(name='en_name',
                         font=Font(name=u'宋体', size=12, bold=False),
                         alignment=Alignment(horizontal='center', vertical='center'))
    to_style = NamedStyle(name='to_style',
                          font=Font(name=u'宋体', size=10, bold=False),
                          alignment=Alignment(horizontal='left', vertical='center'))
    content_style = NamedStyle(name='content_style',
                               font=Font(name=u'宋体', size=10, bold=False),
                               alignment=Alignment(horizontal='right', vertical='center'))
    bold_style = NamedStyle(name='bold_style',
                            font=Font(bold=True))
    border = Border(left=Side(border_style='thin', color=colors.BLACK),
                    right=Side(border_style='thin', color=colors.BLACK),
                    top=Side(border_style='thin', color=colors.BLACK),
                    bottom=Side(border_style='thin', color=colors.BLACK))
    # 颜色
    fill = openpyxl.styles.PatternFill(fill_type='solid', start_color='FFFF00')
    cn_name_1 = NamedStyle(name='cn_name_1',
                           font=Font(name=u'宋体', bold=True),
                           alignment=Alignment(horizontal='center', vertical='center'), fill=fill)
    wb = openpyxl.load_workbook(file_path)
    ws = wb[sheet_name]
    MAX_ROW = ws.max_row
    MAX_COLUMN = ws.max_column
    # 获取在第几列
    max_letter = get_column_letter(MAX_COLUMN)
    ws.insert_rows(1, 5)
    if first:
        ws.cell(1, 1, ext['company1']).style = cn_name
        ws.cell(2, 1, ext['company1Eng']).style = en_name
        ws.cell(4, 1, 'TO：' + ext['to']).style = to_style
        ws.cell(4, MAX_COLUMN, '发票号：' + ext['invoiceNo']).style = content_style
        ws.cell(5, MAX_COLUMN, 'Date：' + ext['date']).style = content_style
    else:
        ws.cell(1, 1, ext['company1']).style = 'cn_name'
        ws.cell(2, 1, ext['company1Eng']).style = 'en_name'
        ws.cell(4, 1, 'TO：' + ext['to']).style = 'to_style'
        ws.cell(4, MAX_COLUMN, '发票号：' + ext['invoiceNo']).style = 'content_style'
        ws.cell(5, MAX_COLUMN, 'Date：' + ext['date']).style = 'content_style'
    ws.merge_cells(f'A1:{max_letter}1')
    ws.merge_cells(f'A2:{max_letter}2')
    total_index = 0
    for row_index in range(1, MAX_ROW + 6):
        for col_index in range(1, MAX_COLUMN + 1):
            cell = ws.cell(row_index, col_index)
            if '总计' in str(cell.value) or '收款信息' in str(cell.value) or '小计' in str(cell.value):
                if str(cell.value) == '总计' or str(cell.value) == '小计':
                    total_index = row_index
                try:
                    cell.style = bold_style
                except:
                    cell.style = 'bold_style'
                if str(cell.value) == '总计' or str(cell.value) == '小计':
                    try:
                        cell.style = bold_style
                    except:
                        cell.style = 'bold_style'
    for row_index in range(6, total_index + 1):
        for col_index in range(1, MAX_COLUMN + 1):
            ws.cell(row_index, col_index).border = border
    # 先找出总计在第几行
    data_row_index = 0
    for row_index in range(1, MAX_ROW + 6):
        for col_index in range(1, MAX_COLUMN + 1):
            cell = ws.cell(row_index, col_index)
            if '总计' in str(cell.value):
                data_row_index = row_index
                break
        if data_row_index > 0:
            break
    has_use = False
    for currency in feeCurrency_list:
        # 在前面已经按照币种排好序了，通过币种找出表头需要合并币种的单元格
        merge_same_currency(ws, currency, MAX_COLUMN, data_row_index, has_use, cn_name_1, border)
        has_use = True
    wb.save(file_path)
    wb.close()


def merge_same_currency(ws, currency, MAX_COLUMN, data_row_index, has_use, cn_name_1, border):
    if data_row_index == 0:
        return
    column_start_index = 0
    column_end_index = 0
    start = False
    # 找到相同币种的开始单元格和结束单元格
    for index in range(1, MAX_COLUMN + 1):
        cell = ws.cell(6, index)
        if currency in str(cell.value) and not start:
            start = True
            column_start_index = index
            column_end_index = index + 1
        elif currency not in str(cell.value) and start:
            column_end_index = index
            break
        elif start:
            column_end_index = index + 1
    # 合并单元格
    if column_start_index > 0 and column_end_index > column_start_index:
        start_letter = get_column_letter(column_start_index)
        end_letter = get_column_letter(column_end_index - 1)
        ws.merge_cells(f'{start_letter}{data_row_index}:{end_letter}{data_row_index}')
        if not has_use:
            try:
                ws.cell(data_row_index, column_start_index).style = cn_name_1
            except:
                ws.cell(data_row_index, column_start_index).style = 'cn_name_1'
        else:
            ws.cell(data_row_index, column_start_index).style = 'cn_name_1'
        ws.cell(data_row_index, column_start_index).border = border


def addToCurrencyTotal(feeColumn, feeCount, currencyAccount):
    for cu in currencyAccount.keys():
        if cu in feeColumn:
            currencyAccount[cu] = '%.2f' % (float(currencyAccount[cu]) + float(feeCount))
            break


# 生成每个sheet的df
def generate_df(type, api, exportColumns, feeExportColumns, moneyTypeList, extInfo, data, fee_key):
    # 组建导出文件列头数据
    excelColumns = []
    for column in exportColumns:
        excelColumns.append(column)
    for column in feeExportColumns:
        # 添加费用字段
        excelColumns.append(column)
    # 创建账单表
    # 添加序号列
    excelColumns.insert(0, "序号")
    df = pd.DataFrame(columns=excelColumns)
    total = {exportColumns[-1]: "小计"}
    # 加入币种汇总
    currencyList = moneyTypeList.values()
    currencyTotal = {}
    for curr in currencyList:
        currencyTotal[curr] = 0
    # 处理费用字段
    for column in feeExportColumns:
        total[column] = 0
    for item in data:
        importRow = {}
        # 处理非费用字段
        for column in exportColumns:
            if column.strip() in fieldMap:
                # 取值属性值
                field = fieldMap[column.strip()]
                if field in item:
                    importRow[column] = item[field]
                    if '供应商' == column:
                        importRow[column] = extInfo['excelCarrier']
        # 处理费用字段
        for column in feeExportColumns:
            if fee_key is None:
                feeList = item['feeList']
            else:
                feeList = item[fee_key]
            for fee in feeList:
                if column in fee:
                    importRow[column] = fee[column]
                    total[column] = ('%.2f' % (float(total[column]) + float(fee[column])))
                    # 加入币种总和
                    addToCurrencyTotal(column, fee[column], currencyTotal)
                    break
        df = df.append(importRow, ignore_index=True)
    # 加入统计
    df = df.append(total, ignore_index=True)
    currency_total = {exportColumns[-1]: "总计"}
    for column in feeExportColumns:
        for item_cu in currencyTotal.keys():
            if item_cu in column:
                prefix = get_prefix(item_cu)
                currency_total[column] = prefix + ('%.2f' % (float(currencyTotal[item_cu])))
                break
    df = df.append(currency_total, ignore_index=True)
    # 添加序号
    row_number_list = []
    size = len(df)
    for i in range(size):
        if i >= len(data):
            row_number_list.append('')
        else:
            row_number_list.append(i + 1)
    df['序号'] = row_number_list
    if "IN" == type:
        # 加入银行账号信息
        for i in range(3):
            nullValue = {exportColumns[0]: " "}
            df = df.append(nullValue, ignore_index=True)
        # 收款信息
        row = {"序号": "收款信息：", exportColumns[7]: "收款信息："}
        df = df.append(row, ignore_index=True)
        # 账户
        row = {"序号": "人民币账户：" + str(extInfo['bankAccountName']),
               exportColumns[7]: "美金账户：" + str(extInfo['bankAccountName'])}
        df = df.append(row, ignore_index=True)
        # 公司名称
        row = {"序号": "公司名称：" + extInfo['rmbCompany'], exportColumns[7]: "公司名称：" + extInfo['usdCompany']}
        df = df.append(row, ignore_index=True)
        # 开户行：
        row = {"序号": "开户行：" + extInfo['rmbBankName'], exportColumns[7]: "开户行：" + extInfo['usdBankName']}
        df = df.append(row, ignore_index=True)
        # 账号：
        row = {"序号": "账号：" + extInfo['rmbBankAccount'],
               exportColumns[7]: "账号：" + extInfo['usdBankAccount']}
        df = df.append(row, ignore_index=True)
    return df


def get_prefix(cu):
    cu_map = {"RMB": "¥", "USD": "$"}
    if cu in cu_map.keys():
        return cu_map[cu]
    return ""


if __name__ == '__main__':
    paramsStr = sys.argv[1]
    # paramsStr = "{\"currencyType\":null,\"type\":\"OUT\",\"userId\":\"1\",\"tableName\":\"zadOrderPayable\",\"ids\":[\"ZO388333053472280576\",\"ZO388285830688542720\",\"ZO388284794938724352\",\"ZO387623926248902656\"],\"exportFeeNames\":null,\"bankInfoId\":null,\"exportColumns\":\"订单编号,所属机构,订舱人,订舱号,柜号,业务类型,计费方式,提货日期,提货地点,数量,货物体积(m?),柜型,班列日期,发车日期,供应商,起始站,目的站,出境口岸,报关方式,货物品类,货物品名,货物重量(KG),ATA到达日期,境外提重日期,超期天数,创建人,创建时间,订单备注,账单备注,运输费,压车费,堆存费,滞箱费,电商报关费,查验费,修箱费,加固费,箱况检查费,提货费,派送费,前端提货费,末端派送费,前程提货费,主页费,续页费\",\"rateListStr\":\"[]\",\"rateList\":null,\"feeNames\":[\"运输费\",\"压车费\",\"堆存费\",\"滞箱费\",\"电商报关费\",\"查验费\",\"修箱费\",\"加固费\",\"箱况检查费\",\"超期堆存费\",\"进区报关费\",\"联单费\",\"核注清单费\",\"装箱费\",\"加固材料费\",\"提空柜费\",\"提柜代付费\",\"重柜上站\",\"贴标\",\"提货费\",\"派送费\",\"前端提货费\",\"末端派送费\",\"前程提货费\",\"主页费\",\"续页费\",\"报关费\",\"报关联单费\"],\"fieldMap\":{\"超期天数\":\"orderBeyondDays\",\"货物重量(KG)\":\"goodsWeight\",\"订单备注\":\"remark\",\"提货日期\":\"pickDate\",\"目的站\":\"destPlace\",\"发车日期\":\"actDepartureTime\",\"货物体积(m?)\":\"volume\",\"数量\":\"quantity\",\"订舱号\":\"bookNumber\",\"订单编号\":\"orderNumber\",\"订舱人\":\"bookName\",\"柜号\":\"cabinetNumber\",\"班列日期\":\"preDepartureTime\",\"创建人\":\"userName\",\"提货地点\":\"pickPlace\",\"账单备注\":\"billRemark\",\"客户\":\"clientName\",\"货物品类\":\"goodsName\",\"柜型\":\"cabinetType\",\"出境口岸\":\"carrierDeparturePort\",\"货物品名\":\"goodsDetailName\",\"业务编号\":\"businessNumber\",\"业务类型\":\"businessCode\",\"报关方式\":\"customsType\",\"ATA到达日期\":\"orderArriveTime\",\"境外提重日期\":\"orderLeaveTime\",\"所属机构\":\"organizationName\",\"计费方式\":\"calculateType\",\"创建时间\":\"createTime\",\"起始站\":\"originPlace\"},\"excelPath\":\"D:\\\\system\\\\file\\\\账单-20221220173946.xlsx\",\"excelType\":null,\"api\":\"http://localhost:9045/api/zadOrder/partExportByTypeQuery\",\"ext\":{\"date\":\"2022-12-20\",\"invoiceNo\":\"202212006\"},\"pickDateStart\":null,\"pickDateEnd\":null,\"actDepartureTimeStart\":null,\"actDepartureTimeEnd\":null,\"createTimeStart\":null,\"createTimeEnd\":null,\"receiveDateStart\":null,\"receiveDateEnd\":null,\"carrierId\":null,\"clientId\":null,\"orderNumber\":null,\"bookNumber\":null,\"carrierDeparturePort\":null,\"originPlace\":null,\"destPlace\":null,\"userName\":null,\"invoice\":null,\"settlement\":null,\"outInvoice\":null,\"outSettlement\":null,\"currency\":\"无需转换\",\"bookName\":null,\"businessNumber\":null,\"preDepartureTimeStart\":null,\"preDepartureTimeEnd\":null,\"organizationId\":null}"
    params = json.loads(paramsStr)
    exportColumnsStr = params['exportColumns']
    feeNames = params['feeNames']
    exportFeeNames = []
    fieldMap = params['fieldMap']
    excelPath = params['excelPath']
    extInfo = params['ext']
    api = params['api']
    # IN OR OUT
    type = params['type']
    # api = r"http://localhost:9045/api/zadOrder/partExportByTypeQuery"
    # extInfo['bankAccountName'] = 'test'
    # extInfo['rmbCompany'] = 'test'
    # extInfo['usdCompany'] = 'test'
    # extInfo['rmbBankName'] = 'test'
    # extInfo['usdBankName'] = 'test'
    # extInfo['rmbBankAccount'] = 'test'
    # extInfo['usdBankAccount'] = 'test'
    # extInfo['invoiceNo'] = 'test'
    # extInfo['date'] = 'test'
    if "currency" in params:
        currency = params['currency']
    else:
        currency = params['currencyType']
    # 币种简写
    moneyTypeList = {"人民币": "RMB", "美元": "USD", "美金": "USD", "欧元": "EUR", "英镑": "GBP", "瑞士法郎": "CHF"}
    # 如果导出的列为空， 则默认导出系统所有列
    if exportColumnsStr is None or len(exportColumnsStr) == 0:
        exportColumns = fieldMap.keys()
    else:
        exportColumns = []
        exportColumns_split = exportColumnsStr.split(",")
        for text in exportColumns_split:
            if text not in exportColumns:
                exportColumns.append(text)
    # 需要导出的费用项
    if "报关费" not in feeNames:
        feeNames.append("报关费")
    if "报关联单费" not in feeNames:
        feeNames.append("报关联单费")
    for name in feeNames:
        if name in exportColumns and name not in exportFeeNames:
            exportFeeNames.append(name)
    # 减去两项特殊费用和所有费用字段
    if "报关费" in exportColumns:
        exportColumns.remove("报关费")
    if "报关联单费" in exportColumns:
        exportColumns.remove("报关联单费")
    for name in feeNames:
        if name in exportColumns:
            exportColumns.remove(name)
    del params['ext']
    params['exportFeeNames'] = exportFeeNames
    result = requests.post(api, json=params)
    data = result.json()["content"]
    # 重新组装费用字段
    # 生成费用的列头数据，这里分两种情况：1如果currency为无需转换，则同一费用可能存在多列（运费(USD),运费(RMB)...）。2如果不是，则只有一列
    # 导出的费用字段命名列表
    feeExportColumns = []
    # 所有币种的总和
    feeCurrency_list = []
    if currency is None or len(currency) == 0 or currency == "无需转换":
        # 处理多种币种
        for item in data:
            feeList = item['feeList']
            if len(feeList) > 0:
                for fee in feeList:
                    feeCurrency = fee['currency']
                    feeName = fee['feeName']
                    if feeCurrency is not None and feeCurrency in moneyTypeList.keys():
                        feeCurrencyEng = moneyTypeList[feeCurrency]
                        # 加入到币种总和列表
                        if feeCurrencyEng not in feeCurrency_list:
                            feeCurrency_list.append(feeCurrencyEng)
                        text = feeName + "(" + feeCurrencyEng + ")"
                        # 新加字段，方便取值
                        fee[text] = fee['feeCount']
                        if text not in feeExportColumns and feeName in exportFeeNames:
                            feeExportColumns.append(text)
    else:
        # 处理转换币种
        for item in data:
            feeList = item['feeList']
            if len(feeList) > 0:
                for fee in feeList:
                    feeCurrency = fee['currency']
                    feeName = fee['feeName']
                    if feeCurrency is not None:
                        # 转换币种
                        feeCurrencyEng = moneyTypeList[currency]
                        # 加入到币种总和列表
                        if feeCurrencyEng not in feeCurrency_list:
                            feeCurrency_list.append(feeCurrencyEng)
                        text = feeName + "(" + feeCurrencyEng + ")"
                        # 新加字段，方便取值
                        fee[text] = fee['feeCount']
                        if text not in feeExportColumns and feeName in exportFeeNames:
                            feeExportColumns.append(text)
    # 费用列表排序，相同币种放在一起
    new_feeExportColumns = []
    for item in feeCurrency_list:
        for fee_item in feeExportColumns:
            if item in fee_item:
                new_feeExportColumns.append(fee_item)
    # 重新赋值给feeExportColumns
    feeExportColumns = new_feeExportColumns
    # 修改于20220114 供应商账单分开每个sheet
    if type == "OUT":
        # 取出所有的供应商
        carrier_name_list = set()
        for item in data:
            carrier_fees = item['feeList']
            for fee in carrier_fees:
                if fee['carrierId'] is not None and len(fee['carrierId']) > 0:
                    carrier_name_list.add(fee['carrierId'])
        # 然后每个供应商作为一个sheet导出
        writer = pd.ExcelWriter(excelPath)
        for carrier in carrier_name_list:
            carrier_data = []
            for item in data:
                carrierName = item['carrierName']
                if carrier not in carrierName:
                    continue
                carrier_fees = item['feeList']
                new_carrier_fees = []
                for fee in carrier_fees:
                    if fee['carrierId'] == carrier:
                        new_carrier_fees.append(fee)
                # 过滤不是当前供应商的费用
                item['fee_list2'] = new_carrier_fees
                # 当费用条数大于0
                if len(item['fee_list2']) > 0:
                    carrier_data.append(item)
            # 把应付账单的供应商替换
            extInfo['excelCarrier'] = carrier
            df = generate_df("OUT", api, exportColumns, feeExportColumns, moneyTypeList, extInfo, carrier_data,
                             "fee_list2")
            df.to_excel(excel_writer=writer, sheet_name=carrier, index=False, header=True)
            writer.save()
        writer.close()
        first = True
        for carrier in carrier_name_list:
            extInfo['company1'] = carrier
            extInfo['company1Eng'] = ''
            extInfo['to'] = '深圳星达国际供应链科技有限公司'
            set_style(excelPath, extInfo, carrier, first, feeCurrency_list)
            first = False
        print(excelPath)
    else:
        df = generate_df("IN", api, exportColumns, feeExportColumns, moneyTypeList, extInfo, data, None)
        # 保存到本地文件
        df.to_excel(excelPath, sheet_name='账务', index=False, header=True)
        # 修改格式
        names = set()
        for item in data:
            # names.add(item['clientName'])
            names.add(item['organizationName'])
        extInfo['company1'] = str(extInfo['bankAccountName'])
        extInfo['company1Eng'] = ''
        extInfo['to'] = ','.join(names)
        set_style(excelPath, extInfo, "账务", True, feeCurrency_list)
        print(excelPath)
